import aioodbc
import app.tools.SafeMonitorTools.m_position_layer_type as smconfig
layertype = smconfig.LayerType
dsn = smconfig.Sqlserver_dsn
pool = None
# 现在默认所有的mineid 也就是矿的id都为 safemonitor
async def initPool():
    global pool
    pool = await aioodbc.create_pool(dsn=dsn,minsize=0)

async def query_all_tree_nodes(parm):
    global pool
    mine_id = parm['mine_id']
    broadcasts = await query_broadcast_admin(mine_id)
    gass = await query_gas_admin(mine_id)
    staffs = await query_staff_admin(mine_id)
    videos = await query_video_admin(mine_id)
    return {'broadcasts':broadcasts,'gass':gass,'staffs':staffs,'videos':videos}

# 查询全部 地下广播节点
async def query_broadcast_admin(mine_id):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM View_Broadcast_Admin WHERE mine_id = ? OR mine_id IS NULL ORDER BY node_id",mine_id)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询全部 安全监控节点
async def query_gas_admin(mine_id):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM View_Gas_Admin WHERE mine_id = ? OR mine_id IS NULL ORDER BY node_id",mine_id)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询已被设置过的 安全监控节点
async def query_gas_data(mine_id,layer):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT D.mine_id as mine_id, D.layer as layer, D.node_id as node_id, D.status as status,D.alarm as alarm,D.real_data as real_data,D.save_time as save_time,I.parent_id as parent_id,I.node_type as node_type,I.x as x,I.y as y,D.real_data as realdata FROM View_Gas_Data D left join View_Gas_Info I on D.node_id = I.node_id WHERE D.mine_id = ? AND D.layer = ?",mine_id,layer)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询全部 人员分站节点
async def query_staff_admin(mine_id):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM View_Staff_Admin WHERE mine_id = ? OR mine_id IS NULL ORDER BY id",mine_id)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询 已经被选择 设置过的人员分站
# 查询全部 人员分站节点
async def query_staff_data(mine_id,layer):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT p.node_id, r.State AS status, r.LastUpdateTime AS save_time, "
                           "r.Count AS count, r.Alarm AS alarm , r.Placement AS placement,r.State AS state FROM M_Position p "
                           "INNER JOIN G_PL_Readers r ON p.node_id = r.id AND p.service_type = 'staff' "
                           "WHERE p.mine_id = ? AND p.layer = ?",mine_id,layer)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询 人员分站的info
async def query_staff_info(node_id,mineId):
    global pool
    print('人员分站的info接口接收到的readerID',node_id)
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT r.id AS rid, r.Count AS count, r.LastUpdateTime AS save_time,r.Alarm AS alarm , r.Placement AS placement,r.State AS state,l.name AS pname,l.department AS department, l.duty AS duty,l.state AS pstate,l.rfid AS rid,l.inTime AS intime FROM G_PL_Readers r left JOIN View_Staff_List l ON l.readerId = r.id WHERE r.id = ?",node_id)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

async def query_broadcast_data(mine_id,layer):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT D.mine_id as mine_id,D.layer as layer,D.node_id as node_id,D.status as status, D.save_time as save_time, D.alarm as alarm, S.region as place,I.parent_id as parent_id,I.node_type as node_type FROM View_Broadcast_Data D left join G_BC_Speakers S on D.node_id = S.sn left join View_Broadcast_Info I on D.node_id = I.node_id WHERE D.mine_id = ? AND D.layer = ?",mine_id,layer)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询 地下广播的info信息
async def query_broadcast_info(node_id,mineId):
    global pool
    print('地下广播info接口获取到的node_id',node_id)
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT I.node_id AS node_id, I.node_type AS node_type, I.place AS place,I.layer AS layer , S.LastUpdateTime AS lastupdatetime,S.State AS state,S.Alarm AS alarm FROM View_Broadcast_Info I left JOIN  G_BC_Speakers S ON I.node_id = S.sn WHERE I.node_id = ? and I.mine_id = ? and I.layer = ?",node_id,mineId,layertype['SMBroadCast'])
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r

# 查询全部 摄像头节点
async def query_video_admin(mine_id):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM View_Video_Admin WHERE mine_id = ? OR mine_id IS NULL ORDER BY ip",mine_id)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r
# 查询 已被设置过的摄像头节点
async def query_video_data(mine_id,layer):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM View_Video_Admin WHERE mine_id = ? AND layer = ? ORDER BY ip",mine_id,layer)
        r = await cur.fetchall()
        await cur.close()
        # await conn.close()
        return r


# 单个查询 m_position 表格信息
async def query_position(mine_id,service_type,node_id,layer):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM M_Position WHERE mine_id = ? AND service_type = ? AND node_id = ? AND layer = ?",mine_id,service_type,node_id,layer)
        r = await cur.fetchall()
        await cur.close()
        await conn.close()
        return r

# 查询全部 m_position 表格信息
async def query_all_position(mine_id):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM M_Position WHERE mine_id = ? AND layer is not NULL ",mine_id)
        r = await cur.fetchall()
        await cur.close()
        await conn.close()
        return r

# 向 m_position表格中 添加position
async def add_position(infoList):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        try:
            await cur.executemany("insert into M_Position(mine_id, node_id,service_type,x,y,layer) values (?,?,?,?,?,?)",infoList)
        # r = await cur.fetchall()
        except aioodbc.DatabaseError as err:
            await conn.rollback()
            return False
        else:
            await conn.commit()
            return True
        finally:
            await cur.close()

# 向 m_position表格中 修改 position
async def update_position(info):
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        try:
            await cur.execute("update M_Position set x=?,y=? where mine_id=? and node_id=? and service_type=? and layer=?",info['x'],info['y'],info['mine_id'],info['node_id'],info['service_type'],info['layer'])
        except aioodbc.DatabaseError as err:
            await conn.rollback()
            return False
        else:
            await conn.commit()
            return True
        finally:
            await cur.close()

# 删除 m_position 数据
async def delete_position(info):
    global pool
    mine_id,node_id,service_type,layer = info.values()
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        try:
            await cur.execute("delete from M_Position where mine_id=? and node_id=? and service_type=? and layer=?",mine_id,node_id,service_type,layer)
        except aioodbc.DatabaseError as err:
            await conn.rollback()
            return False
        else:
            await conn.commit()
            return True
        finally:
            await cur.close()

# 获取 传感器的报警关联的广播站，摄像头的相关信息
async def get_integration():
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute("SELECT * FROM View_Integration")
        r = await cur.fetchall()
        links = {}
        for row in r:
            for node in row.nodes.split(","):
                if node not in links:
                    links[node] = {
                        "readers": set(),
                        "speakers": set(),
                        "videos": set()
                    }
                for reader in row.readers.split(","):
                    links[node]["readers"].add(reader)
                for speaker in row.speakers.split(","):
                    links[node]["speakers"].add(speaker)
                if row.video_id:
                    links[node]["videos"].add(row.video_id)
        for node, value in links.items():
            value["readers"] = list(value["readers"])
            value["speakers"] = list(value["speakers"])
            value["videos"] = list(value["videos"])
        await cur.close()
        # await conn.close()
        return links

async def get_ventilation():
    arrows = []
    tmp_type = ""
    sql = "SELECT * FROM View_Ventilation"
    global pool
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute(sql)
        r = await cur.fetchall()
        for row in r:
            if row.node_type == 16:
                tmp_type = "风速"
            elif row.node_type == 41:
                tmp_type = "风向"
            arrows.append(
                {
                    "id": row.node_id,
                    "type": tmp_type,
                    "placement": row.node_place,
                    "direction": row.real_data
                }
            )
        await cur.close()
        # await conn.close()
        return arrows


# 获取 断电区域的相关信息（包括该区域所关联的传感器列表）
async def get_regions(mine_id,layer):
    global pool
    regions = []
    sql = "SELECT * FROM M_Region WHERE mine_id = ? AND layer = ?"
    params = (mine_id, layer)
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        await cur.execute(sql,params)
        r = await cur.fetchall()
        for row in r:
            regions.append(
                {
                    "id": row.region_id,
                    "name": row.region_name,
                    "nodes": row.related_nodes
                }
            )
        await cur.close()
        # await conn.close()
        return regions

# 向 kj83 里面添加 断电区域
async def add_regions(mine_id,region_id,layer):
    global pool
    sql = "insert into M_Region (mine_id,region_id,layer) values (?,?,?);"
    params = (mine_id, region_id,layer)
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        try:
            await cur.execute(sql,params)
            await conn.commit()
        # 用于捕获错误
        except BaseException as err:
            await conn.rollback()
            return False
        else:
            return True
        finally:
            await cur.close()

# 向 kj83  修改断电区域与断馈电传感器的关联关系
async def put_regions(mine_id,id,nodeid):
    global pool
    sql = "update M_Region set related_nodes = ? where mine_id = ? and region_id = ?;"
    params = (nodeid, mine_id,id)
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        try:
            await cur.execute(sql,params)
            await conn.commit()
        # 用于捕获错误
        except BaseException as err:
            await conn.rollback()
            return False
        else:
            return True
        finally:
            await cur.close()

# 向 kj83  修改断电区域与断馈电传感器的关联关系
async def delete_regions(mine_id,id):
    global pool
    sql = "delete from M_Region where mine_id = ? and region_id = ?;"
    params = (mine_id,id)
    async with pool.acquire() as conn:
        cur = await conn.cursor()
        try:
            await cur.execute(sql,params)
            await conn.commit()
        # 用于捕获错误
        except BaseException as err:
            await conn.rollback()
            return False
        else:
            return True
        finally:
            await cur.close()